﻿using TodoApp.Data;
using TodoApp.Models;
using TodoApp.Services;
using EFCoreRepository.DbContexts;
using EFCoreRepository.Extensions;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;
using System.Data;
using Microsoft.Extensions.DependencyInjection;
using TodoApp.Repository;



namespace TodoApp.CreateDb
{
    internal class Program
    {
        static void Main(string[] args)
        {
            const string connectionString = "Server=MS-ZTSWQBFEPXRW\\SQLEXPRESS;Database=Todo;TrustServerCertificate=True;Integrated Security=True;";
            //IServiceCollection services = new ServiceCollection();
            //services.AddDbContext<TodoContext>(options => 
            //            options.UseSqlServer(connectionString));
            //services.AddSqlServer<TodoContext>(connectionString);
            ////services.AddSingleton<TodoContext>();
            //services.AddSingleton<TodoItemRepos>();
            //var serviceProvider = services.BuildServiceProvider();
            //TodoContext db = serviceProvider.GetRequiredService<TodoContext>();
            //TodoItemRepos repos = serviceProvider.GetRequiredService<TodoItemRepos>();

            TodoContext db = new TodoContext();
            TodoItemRepos repos = new TodoItemRepos();

            //sql
            QueryWithSqlDemo(db);
            InsertWithSqlDemo(db);
            UpdateWithSqlDemo(db);
            DeleteWithSqlDemo(db);

            //orm
            InsertWithORMDemo(db);
            UpdateWithORMDemo(db);
            DeleteWithORMDemo(db);

            //repos
            QueryAllDemo(db,repos);
            InsertDemo(db, repos); 
            UpdateDemo(db, repos); 
            DeleteDemo(db, repos); 
        }

        static void QueryWithSqlDemo(TodoContext db)
        {
            string sql = "select * from [dbo].[TodoItem] where IsComplete=@IsComplete";
            IDictionary<string, object> dict = new Dictionary<string, object>();
            dict["@IsComplete"] = false;
            SqlParameter[] parameters = dict.ToSqlParameters();
            var todoitems = db.SqlQuery<TodoItem>(sql, parameters);

            foreach (var item in todoitems)
            {
                Console.WriteLine($"Id:{item.Id}-Name:{item.Name}-IsComplete:{item.IsComplete}");
            }

        }

        static void InsertWithSqlDemo(TodoContext db)
        {
            string sql = @"INSERT INTO [dbo].[TodoItem] 
                ([Id] ,[Name],[IsComplete])
                VALUES(@Id,@Name,@IsComplete)";
            IDictionary<string, object> dict = new Dictionary<string, object>();
            int Id = 52;
            if (IsExists(db,Id))
            {
                Console.WriteLine("The record already exists");
                return;
            }
            string name = "Huang";
            bool iscomplete = false;
            dict.Add("@Id", Id);
            dict.Add("@Name", name);
            dict.Add("@IsComplete", iscomplete);

            SqlParameter[] parameters = dict.ToSqlParameters();
            db.ExecuteSql(sql, parameters);
        }

        static void DeleteWithSqlDemo(TodoContext db)
        {
            string sql = @"DELETE FROM [dbo].[TodoItem]
             WHERE Id=@Id";
            IDictionary<string, object> dict = new Dictionary<string, object>();
            int Id = 52;
            dict.Add("@Id", Id);
            if (IsExists(db, Id))
            {
                SqlParameter[] parameters = dict.ToSqlParameters();
                db.ExecuteSql(sql, parameters);
                return;
            }
            else
            {
                Console.WriteLine("The Record not existing");
            }            
        }

        static void UpdateWithSqlDemo(TodoContext db)
        {
            string sql = @"UPDATE [dbo].[TodoItem] SET  [Name] = @Name,
                           [IsComplete] = @IsComplete
                           WHERE Id=@Id";
            IDictionary<string, object> dict = new Dictionary<string, object>();
            int Id = 52;
            string name = "Liu";
            bool iscomplete = true;
            dict.Add("@Id", Id);
            dict.Add("@Name", name);
            dict.Add("@IsComplete", iscomplete);
            if (IsExists(db, Id))
            {              

                SqlParameter[] parameters = dict.ToSqlParameters();
                db.ExecuteSql(sql, parameters);
                return;
            }
            else
            {
                Console.WriteLine("The Record not existing");
            }

        }

        static bool IsExists(TodoContext db,int Id)
        {
            string sql = @"select * from [dbo].[TodoItem] where Id=@Id";
            IDictionary<string, object> dict = new Dictionary<string, object>();
            dict.Add("@Id", Id);
            SqlParameter[] parameters = dict.ToSqlParameters();
            var todoitems = db.SqlQuery<TodoItem>(sql, parameters);
            if (todoitems.Any()) {
                return true;
            }
            return false;

        }

        static bool IsExistsWithORM(TodoContext db, int Id)
        {
            var todoitems = db.TodoItems.Where(x => x.Id == Id);
            if (todoitems.Any())
            {
                return true;
            }
            return false;
        }

        static void InsertWithORMDemo(TodoContext db)
        {
            TodoItem item = new TodoItem();
            item.Id = 53;
            item.Name = "ding";
            item.IsComplete = true;
            if (IsExistsWithORM(db, item.Id))
            {
                Console.WriteLine("The record already exists");
                return;
            }
            else { 
                db.TodoItems.Add(item);
                db.SaveChanges();
            }
        }

        static void UpdateWithORMDemo(TodoContext db)
        {
            int Id = 53;
            
            if (!IsExistsWithORM(db, Id))
            {
                Console.WriteLine("The record not existing");
                return;
            }
            else
            {
                var entity = db.TodoItems.FirstOrDefault(x => x.Id==Id);
                if (entity != null)
                {
                    entity.IsComplete = true;
                    entity.Name = "Luo";
                    db.SaveChanges();
                }
            }
        }

        static void DeleteWithORMDemo(TodoContext db)
        {
            int Id = 53;

            if (!IsExistsWithORM(db, Id))
            {
                Console.WriteLine("The record not existing");
                return;
            }
            else
            {
                var entity = db.TodoItems.FirstOrDefault(x => x.Id ==Id);
                if(entity != null) {    
                    db.TodoItems.Remove(entity);    
                    db.SaveChanges();
                }
            }
        }

        static void QueryAllDemo(TodoContext db,TodoItemRepos repos)
        {
            var srv = new TodoService(db,repos);
            var todos = srv.QueryAllItems();
            foreach (var item in todos)
            {
                Console.WriteLine($"Id:{item.Id}-Name:{item.Name}-IsComplete:{item.IsComplete}");
            }
        }

        static void InsertDemo(TodoContext db, TodoItemRepos repos)
        {
            var svr = new TodoService(db, repos); 
            TodoItem item = new TodoItem();
            item.Id = 54;
            item.Name = "Test";
            item.IsComplete = true;
            if (svr.FindById(item.Id) != null)
            {
                Console.WriteLine("The Record already exists");
                return;
            }
            else
            {
                try
                {
                    svr.Insert(item);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }

        static void UpdateDemo(TodoContext db, TodoItemRepos repos)
        {

            var svr = new TodoService(db, repos);
            int Id = 54;
            var item = svr.FindById(Id);
            if (item == null)
            {
                Console.WriteLine("The Record do not exist");
                return;
            }
            else
            {
                try
                {
                    item.Name = "Gao";
                    item.IsComplete = false;
                    svr.Update(item);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }


        static void DeleteDemo(TodoContext db, TodoItemRepos repos)
        {
            var svr = new TodoService(db, repos); ;
            int Id = 54;
            var item = svr.FindById(Id);
            if (item == null)
            {
                Console.WriteLine("The Record do not exist");
                return;
            }
            else
            {
                try
                {
                    svr.Delete(item);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }


    }
}
